﻿using Corina.Datamodel.Entities;
using Corina.Enumerations;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class DeviceService : ServiceBase
    {
        public DataTable GetFetchedItems(string connectionString, int kind, string deviceSn, int saleFlag, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY td.Id) AS ROW_NUMBER,td.*,tdk.Name AS Kind,tddtt.Title AS DataModule ");
            builder.Append("FROM t_device AS td ");
            builder.Append("LEFT JOIN t_device_kind AS tdk ON tdk.id = td.DeviceKindId ");
            builder.Append("LEFT JOIN t_device_data_transfer_type AS tddtt ON tddtt.Id = td.DataTransferTypeId ");
            builder.Append("WHERE 1=1 ");

            #region Sn
            builder.Append("AND td.DeviceSn LIKE @Sn ");
            paras.Add(new SqlParameter("@Sn", string.Format("%{0}%", deviceSn)));
            if (kind > 0)
            {
                builder.Append("AND td.DeviceKindId =@Kind ");
                paras.Add(new SqlParameter("@Kind", kind));
            }
            if (saleFlag != 0)
            {
                builder.AppendFormat("AND td.DeviceSn {0} IN (SELECT tds.DeviceSn FROM t_device_sale AS tds) ", saleFlag > 0 ? "" : "NOT");
            }

            #endregion

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public DataTable GetUsersDevice(string connectionString, int userId, int kind, string deviceSn, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY td.Id) AS ROW_NUMBER,td.*,tdk.Name AS Kind,tddtt.Title AS Transfer, tds.SaleDate, tds.Price ");
            builder.Append(" FROM t_device AS td ");
            builder.Append("LEFT JOIN t_device_kind AS tdk ON tdk.id = td.DeviceKindId ");
            builder.Append("LEFT JOIN t_device_data_transfer_type AS tddtt ON tddtt.Id = td.DataTransferTypeId ");
            builder.Append("LEFT JOIN t_device_sale AS tds ON tds.DeviceSn = td.DeviceSn ");
            builder.Append("WHERE 1=1 ");

            #region Condition
            builder.Append("AND tds.UserId = @User ");
            paras.Add(new SqlParameter("@User", userId));
            builder.Append("AND td.DeviceSn LIKE @Sn ");
            paras.Add(new SqlParameter("@Sn", string.Format("%{0}%", deviceSn)));
            if (kind > 0)
            {
                builder.Append("AND td.DeviceKindId =@Kind ");
                paras.Add(new SqlParameter("@Kind", kind));
            }
            
            #endregion

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public bool Update(string connectionString, DeviceInfo entity, EntityOperation operation)
        {
            return DbOperator.UpdateEntity(connectionString, entity, operation);
        }
    }
}
